package excelFileReader;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

/**
 * @author Idan
 *This class is used to perform ETL on Engrave reports 
 */
public class ExcelReaderEngraves
{
	
	private static Connection conn = null;
	private static ArrayList<Object> row = new ArrayList<Object>();

	
	public void processOneSheet(String filename) throws Exception {
		OPCPackage pkg = OPCPackage.open(filename);
		XSSFReader r = new XSSFReader( pkg );
		SharedStringsTable sst = r.getSharedStringsTable();

		XMLReader parser = fetchSheetParser(sst);

		// rId2 found by processing the Workbook
		// Seems to either be rId# or rSheet#
		InputStream sheet2 = r.getSheet("rId2");
		InputSource sheetSource = new InputSource(sheet2);
		parser.parse(sheetSource);
		sheet2.close();
	}

	public void processAllSheets(String filename) throws Exception 
	{
		OPCPackage pkg = OPCPackage.open(filename);
		XSSFReader r = new XSSFReader( pkg );
		SharedStringsTable sst = r.getSharedStringsTable();
		
		XMLReader parser = fetchSheetParser(sst);

		Iterator<InputStream> sheets = r.getSheetsData();
		while(sheets.hasNext())
		{
			System.out.println("Processing new sheet:\n");
			InputStream sheet = sheets.next();
			InputSource sheetSource = new InputSource(sheet);
			parser.parse(sheetSource);
			sheet.close();
			System.out.println("");
		}
	}

	public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException 
	{
		XMLReader parser =
			XMLReaderFactory.createXMLReader(
					"org.apache.xerces.parsers.SAXParser"
			);
		ContentHandler handler = new SheetHandler(sst);
		parser.setContentHandler(handler);
		return parser;
	}

	/** 
	 * See org.xml.sax.helpers.DefaultHandler javadocs 
	 */
	public static class SheetHandler extends DefaultHandler
	{
		private SharedStringsTable sst;
		private String lastContents;
		private boolean nextIsString;
		
		private SheetHandler(SharedStringsTable sst) 
		{
			this.sst = sst;
		}
		
		public void startElement(String uri, String localName, String name,
				Attributes attributes) throws SAXException 
				{
			// c => cell
			if(name.equals("c")) {
				// Print the cell reference
				System.out.print(attributes.getValue("r") + " - ");
				
				if ( attributes.getValue("r").startsWith("J") ) // Test if we reached the last column
				{
					//row.add("null"); // Empty comment
				// Time to dump the line and start a new one
						System.out.print("NEW LINE!\n");
						//row.add(EOL);
					
				}			
				
				
				// Figure out if the value is an index in the SST
				String cellType = attributes.getValue("t");
				if(cellType != null && cellType.equals("s"))
				{	
					nextIsString = true;
					
				} else {
					nextIsString = false;
				}
			}
			// Clear contents cache
			lastContents = "";
		}
		
		public void endElement(String uri, String localName, String name)
				throws SAXException
				{
			// Process the last contents as required.
			// Do now, as characters() may be called more than once
			if(nextIsString) {
				int idx = Integer.parseInt(lastContents);			
				lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
				
				
            nextIsString = false;
			}

			// v => contents of a cell
			// Output after we've seen the string contents
			if(name.equals("v"))
			{
				row.add(lastContents); // Add value to row
				System.out.println("added " + lastContents);
			
			}
		}

		

		public void characters(char[] ch, int start, int length)
				throws SAXException
				{
			lastContents += new String(ch, start, length);
		}
	}
	

	
	
	public void test() throws SQLException
	{
		Statement stmt = null;
		ResultSet rs = null;
	    stmt = conn.createStatement();
	    rs = stmt.executeQuery("SELECT * FROM status");
	    
	    while(rs.next())
	    {
	    	
	    	System.out.println(rs.getString(1));
	    	System.out.println(rs.getString(2));

	    
	    }
	    
	    if (rs != null) {
	        try {
	            rs.close();
	        } catch (SQLException sqlEx) { } // ignore
	        rs = null;
	    }
	    if (stmt != null) {
	        try {
	            stmt.close();
	        } catch (SQLException sqlEx) { } // ignore
	        stmt = null;
	    }
	}
	
	public static void main(String[] args) throws Exception 
	{
		ExcelReaderEngraves howto = new ExcelReaderEngraves();
		
		
		
		try
		{
		    conn = DriverManager.getConnection("jdbc:mysql://localhost/hanitdb?" +
		                                   "user=root&password=&useUnicode=true&characterEncoding=UTF-8");
		    
		    
		  howto.processAllSheets("orders2013.xlsx");
		  howto.processAllSheets("orders2014.xlsx");
	        
	       
				


		}
		 catch (SQLException ex)
		 {
			    // handle any errors
			    System.out.println("SQLException: " + ex.getMessage());
			    System.out.println("SQLState: " + ex.getSQLState());
			    System.out.println("VendorError: " + ex.getErrorCode());
			}
		 sendToDB(row); // commit to DB
		
	}

	private static void sendToDB(ArrayList<Object> row) throws SQLException 
	{

		Statement stmt = conn.createStatement();

	
		int i=0;
		int result;

		
		while (i<(row.size()))
			{
			
			Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(Double.parseDouble((String)row.get(i+0)));

			java.sql.Date dateDB = new java.sql.Date(date.getTime());
			
			Format formatter = new SimpleDateFormat("dd/MM/yyyy");
			String completeDate = formatter.format(date);
		
			
			System.out.println("Date is " + completeDate);
		
			row.set(i+0, dateDB);
			
			
			System.out.println("Commiting: orderID = " + row.get(i+3) + " line = " + row.get(i+4) + " employeeID = " + row.get(i+2)  + " sideA = " + row.get(i+7)   + " sideB = " + row.get(i+8)   +"\n freeComment = " + row.get(i+9));			
			if (((String)row.get(i+9)).contentEquals("null"))
			{
				result = stmt.executeUpdate("INSERT IGNORE INTO engrave(orderID,engraveDate,line,employeeID,sideAquantity,sideBquantity,shift) VALUES('" + row.get(i+3) + "','" + row.get(i+0) +"','" + row.get(i+4) + "','" + row.get(i+2) + "','" + row.get(i+7) + "','" + row.get(i+8) + "','" + row.get(i+1) + "');");
				
			}
			else
			{
				result = stmt.executeUpdate("INSERT IGNORE INTO engrave(orderID,engraveDate,line,employeeID,sideAquantity,sideBquantity,freeText,shift) VALUES('" + row.get(i+3) + "','" + row.get(i+0) +"','" + row.get(i+4) + "','" + row.get(i+2) + "','" + row.get(i+7) + "','" + row.get(i+8) + "','" + row.get(i+9) + "','" + row.get(i+1) + "');");
			}
			

			
			if (result !=1)
			System.out.println("failed!");
			else
			System.out.println("Great success!");	
			
			i=i+10;

		}
		
		stmt.close();
	

	}

}
